package wpwork.mapper;

import org.apache.ibatis.annotations.*;
import wpwork.base.mapper.SuperMapper;
import wpwork.entity.WorkPersonJobEntity;
import wpwork.model.TaskInfoVO;
import wpwork.model.WorkPersonJobDTO;
import wpwork.model.WorkPersonJobQueryVO;
import wpwork.model.WorkPersonJobVO;

import java.util.List;
import java.util.Map;

/**
 *
 * @author wpwork开发平台组
 * @version V3.1.0
 * @copyright 武汉维普科技有限公司
 * @date 2021/3/16 9:47
 */

@Mapper
public interface WorkPersonJobMapper extends SuperMapper<WorkPersonJobEntity> {


    @Insert("INSERT INTO work_personjob (\n" +
            "        f_id,\n" +
            "        f_type,\n" +
            "        f_resources_id,\n" +
            "        f_job_user_id,\n" +
            "        f_jobinfo,\n" +
            "        f_execution_date,\n" +
            "        f_enabled_mark,\n" +
            "        f_sort_code,\n" +
            "        f_creator_time,\n" +
            "        f_creator_user_id,\n" +
            "        f_last_modify_time,\n" +
            "        f_last_modify_user_id,\n" +
            "        f_delete_mark,\n" +
            "        f_tenant_id\n" +
            "    )\n" +
            "    SELECT\n" +
            "        MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::VARCHAR AS f_id,\n" +
            "        '考勤'::VARCHAR AS f_type,\n" +
            "        r.f_resources_id,\n" +
            "        (terminal.value->>'key')::VARCHAR AS f_job_user_id,\n" +
            "        day_data.times_json AS f_jobinfo, \n" +
            "        day_data.exec_date::TIMESTAMP AS f_execution_date,\n" +
            "        COALESCE(r.f_enabled_mark, 1) AS f_enabled_mark,\n" +
            "        r.f_sort_code,\n" +
            "        COALESCE(r.f_creator_time, NOW()) AS f_creator_time,\n" +
            "        '349057407209541'::VARCHAR AS f_creator_user_id,\n" +
            "        COALESCE(r.f_last_modify_time, NOW()) AS f_last_modify_time,\n" +
            "        r.f_last_modify_user_id,\n" +
            "        COALESCE(r.f_delete_mark, 0) AS f_delete_mark,\n" +
            "        r.f_tenant_id\n" +
            "    FROM work_rsattendance r,\n" +
            "        JSONB_ARRAY_ELEMENTS(r.f_terminal::JSONB) AS terminal(value),\n" +
            "        LATERAL (\n" +
            "            SELECT DISTINCT\n" +
            "                SUBSTRING((t.item->>'start'), 1, 10) AS date_str\n" +
            "            FROM JSONB_ARRAY_ELEMENTS(r.f_time::JSONB) AS t(item)\n" +
            "        ) AS unique_dates(date_str),\n" +
            "        LATERAL (\n" +
            "            SELECT\n" +
            "                unique_dates.date_str::DATE AS exec_date,\n" +
            "                JSONB_AGG(\n" +
            "                    JSONB_BUILD_OBJECT(\n" +
            "                        'start', t.item->>'start',\n" +
            "                        'end', t.item->>'end'\n" +
            "                    )\n" +
            "                ) FILTER (\n" +
            "                    WHERE SUBSTRING(t.item->>'start', 1, 10) = unique_dates.date_str\n" +
            "                ) AS times_json\n" +
            "            FROM JSONB_ARRAY_ELEMENTS(r.f_time::JSONB) AS t(item)\n" +
            "        ) AS day_data(exec_date, times_json)")
    void generatePersonJobFromAttendance();

    @Results(id = "AttendanceRecordResultMap", value = {
            @Result(property = "id", column = "f_id"),
            @Result(property = "type", column = "f_type"),
            @Result(property = "resourcesId", column = "f_resources_id"),
            @Result(property = "jobUserId", column = "f_job_user_id"),
            @Result(property = "realName", column = "f_real_name"), // 来自 base_user
            @Result(property = "imageurl", column = "f_imageurl"),
            @Result(property = "videourl", column = "f_videourl"),
            @Result(property = "jobinfo", column = "f_jobinfo"),
            @Result(property = "remark", column = "f_remark"),
            @Result(property = "enabledMark", column = "f_enabled_mark"),
            @Result(property = "sortCode", column = "f_sort_code"),
            @Result(property = "creatorTime", column = "f_creator_time"),
            @Result(property = "creatorUserId", column = "f_creator_user_id"),
            @Result(property = "lastModifyTime", column = "f_last_modify_time"),
            @Result(property = "lastModifyUserId", column = "f_last_modify_user_id"),
            @Result(property = "deleteTime", column = "f_delete_time"),
            @Result(property = "deleteUserId", column = "f_delete_user_id"),
            @Result(property = "deleteMark", column = "f_delete_mark"),
            @Result(property = "tenantId", column = "f_tenant_id"),
            @Result(property = "executionDate", column = "f_execution_date"),
            @Result(property = "location", column = "f_location"),
            @Result(property = "begindate", column = "f_begindate"),
            @Result(property = "enddate", column = "f_enddate"),
            @Result(property = "text", column = "f_text"),
            @Result(property = "executionStatus", column = "f_execution_status"),
            @Result(property = "instantperson", column = "f_instantperson"),
            @Result(property = "executionTime", column = "f_execution_time"),
            @Result(property = "executionLocation", column = "f_execution_location"),
            @Result(property = "locationDesc", column = "f_location_desc"),
            @Result(property = "taskNumber", column = "f_task_number")
    })
    @Select("SELECT b.f_real_name,* FROM work_personjob a left JOIN base_user b ON a.f_job_user_id = b.f_id  WHERE f_type = '考勤' ORDER BY  a.f_creator_time DESC ")
    List<WorkPersonJobVO> attendanceStatistics();


    @ResultMap("AttendanceRecordResultMap")
    @Select("SELECT b.f_real_name,* FROM work_personjob a left JOIN base_user b ON a.f_job_user_id = b.f_id  WHERE f_type = '考勤' AND (f_begindate >= #{startTime}::timestamp OR f_enddate >= #{startTime}::timestamp) ORDER BY  a.f_creator_time DESC ")
    List<WorkPersonJobVO> attendanceStatisticsStartTime(@Param("startTime") String startTime);

    @ResultMap("AttendanceRecordResultMap")
    @Select("SELECT b.f_real_name,* FROM work_personjob a left JOIN base_user b ON a.f_job_user_id = b.f_id  WHERE f_type = '考勤' AND (f_begindate <=#{endTime}::timestamp OR f_enddate <=#{endTime}::timestamp) ORDER BY  a.f_creator_time DESC ")
    List<WorkPersonJobVO> attendanceStatisticsEndTime(@Param("endTime") String endTime);

    @ResultMap("AttendanceRecordResultMap")
    @Select("SELECT b.f_real_name,* FROM work_personjob a left JOIN base_user b ON a.f_job_user_id = b.f_id  WHERE f_type = '考勤' AND (f_begindate >= #{startTime}::timestamp OR f_enddate >= #{startTime}::timestamp) AND (f_begindate <=#{endTime}::timestamp OR f_enddate <=#{endTime}::timestamp) ORDER BY  a.f_creator_time DESC ")
    List<WorkPersonJobVO> attendanceStatisticsStartTimeAndEndTime(@Param("startTime") String startTime,@Param("endTime") String endTime);

    @Results({
            @Result(property = "taskNumber", column = "f_task_number"),
            @Result(property = "id", column = "f_id"),
            @Result(property = "text", column = "f_text"),
            @Result(property = "jobinfo", column = "f_jobinfo"),
            @Result(property = "imageurl", column = "f_imageurl"),
            @Result(property = "remark", column = "f_remark"),
            @Result(property = "instantperson", column = "f_instantperson"),
            @Result(property = "executionStatus", column = "f_execution_status"),
            @Result(property = "jobUserId", column = "f_job_user_id"),
            @Result(property = "account", column = "f_account"),
            @Result(property = "realName", column = "f_real_name")
    })
    @Select("select a.f_task_number,a.f_id,a.f_text,a.f_jobinfo,a.f_imageurl,a.f_remark,a.f_instantperson,a.f_execution_status,a.f_job_user_id,b.f_account,b.f_real_name " +
            "from work_personjob a inner join base_user b " +
            "on a.f_task_number=#{taskNum} " +
            "and a.f_job_user_id=b.f_id " +
            "order by a.f_instantperson ")
    List<TaskInfoVO> getInfoByTaskNum(String taskNum);

    @Results({
            @Result(property = "taskNumber", column = "f_task_number"),
            @Result(property = "id", column = "f_id"),
            @Result(property = "text", column = "f_text"),
            @Result(property = "jobinfo", column = "f_jobinfo"),
            @Result(property = "imageurl", column = "f_imageurl"),
            @Result(property = "remark", column = "f_remark"),
            @Result(property = "instantperson", column = "f_instantperson"),
            @Result(property = "executionStatus", column = "f_execution_status"),
            @Result(property = "jobUserId", column = "f_job_user_id"),
            @Result(property = "account", column = "f_account"),
            @Result(property = "realName", column = "f_real_name")
    })
    @Select("select * from work_personjob where f_task_number=#{taskNum}")
    List<WorkPersonJobEntity> getInfoListByTaskNum(String taskNum);


    /**
     * 分页查询工作人员作业记录
     */
    @Select({"<script>",
            "SELECT ",
            "a.f_id as fId, ",
            "a.f_text as fText, ",
            "a.f_begindate as fBegindate, ",
            "a.f_enddate as fEnddate, ",
            "a.f_execution_status as fExecutionStatus, ",
            "a.f_task_number as fTaskNumber, ",
            "a.f_type as fType, ",
            "b.f_real_name as fRealName, ",
            "b.f_account as fAccount, ",
            "case when a.f_instantperson = 1 then '负责人' else '' end as fInstantperson ",
            "FROM work_personjob a ",
            "INNER JOIN base_user b ON a.f_job_user_id = b.f_id ",
            "WHERE 1=1 ",
            "<if test='query.fType != null and query.fType != \"\"'>",
            "   AND a.f_type = #{query.fType} ",
            "</if>",
            "<if test='query.fType == null or query.fType == \"\"'>",
            "   AND a.f_type IN ('协同任务', '个人任务') ",
            "</if>",
            "<if test='query.fText != null and query.fText != \"\"'>",
            "   AND a.f_text LIKE '%' || #{query.fText} || '%' ",
            "</if>",
            "<if test='query.fExecutionStatus != null and query.fExecutionStatus != \"\"'>",
            "   AND a.f_execution_status = #{query.fExecutionStatus} ",
            "</if>",
            "<if test='query.fRealName != null and query.fRealName != \"\"'>",
            "   AND b.f_real_name LIKE '%' || #{query.fRealName} || '%' ",
            "</if>",
            "<if test='query.fAccount != null and query.fAccount != \"\"'>",
            "   AND b.f_account LIKE '%' || #{query.fAccount} || '%' ",
            "</if>",
            "<if test='query.fTaskNumber != null and query.fTaskNumber != \"\"'>",
            "   AND a.f_task_number LIKE '%' || #{query.fTaskNumber} || '%' ",
            "</if>",
            "<if test='query.startDate != null'>",
            "   AND a.f_begindate &gt;= #{query.startDate} ",
            "</if>",
            "<if test='query.endDate != null'>",
            "   AND a.f_begindate &lt;= #{query.endDate} ",
            "</if>",
            "ORDER BY a.f_begindate DESC, a.f_task_number ",
            "LIMIT #{size} OFFSET #{offset}",
            "</script>"})
    List<WorkPersonJobDTO> selectWorkPersonJobPage(@Param("query") WorkPersonJobQueryVO query,
                                                   @Param("offset") Long offset,
                                                   @Param("size") Integer size);

    /**
     * 查询总记录数
     */
    @Select({"<script>",
            "SELECT COUNT(*) ",
            "FROM work_personjob a ",
            "INNER JOIN base_user b ON a.f_job_user_id = b.f_id ",
            "WHERE 1=1 ",
            "<if test='query.fType != null and query.fType != \"\"'>",
            "   AND a.f_type = #{query.fType} ",
            "</if>",
            "<if test='query.fType == null or query.fType == \"\"'>",
            "   AND a.f_type IN ('协同任务', '个人任务') ",
            "</if>",
            "<if test='query.fText != null and query.fText != \"\"'>",
            "   AND a.f_text LIKE '%' || #{query.fText} || '%' ",
            "</if>",
            "<if test='query.fExecutionStatus != null and query.fExecutionStatus != \"\"'>",
            "   AND a.f_execution_status = #{query.fExecutionStatus} ",
            "</if>",
            "<if test='query.fRealName != null and query.fRealName != \"\"'>",
            "   AND b.f_real_name LIKE '%' || #{query.fRealName} || '%' ",
            "</if>",
            "<if test='query.fAccount != null and query.fAccount != \"\"'>",
            "   AND b.f_account LIKE '%' || #{query.fAccount} || '%' ",
            "</if>",
            "<if test='query.fTaskNumber != null and query.fTaskNumber != \"\"'>",
            "   AND a.f_task_number LIKE '%' || #{query.fTaskNumber} || '%' ",
            "</if>",
            "<if test='query.startDate != null'>",
            "   AND a.f_begindate &gt;= #{query.startDate} ",
            "</if>",
            "<if test='query.endDate != null'>",
            "   AND a.f_begindate &lt;= #{query.endDate} ",
            "</if>",
            "</script>"})
    Long selectWorkPersonJobCount(@Param("query") WorkPersonJobQueryVO query);

}
